/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Category;
import model.Product;

/**
 *
 * @author Trang
 */
public class InventoryDAO {
    private Connection con;
    public ProductDAO productDAO;
    public InventoryDAO()
    {
        con = DBFactory.createConnection("onlineshopping");
    }
    public ArrayList<Product> getProductForInventory(Integer sort)
    {
        ArrayList<Product> result = new ArrayList<Product>();
                try {
                String sortby = "";
                if(sort == 1)
                    sortby = "code";
                else if(sort == 2)
                    sortby = "name";
                else if(sort == 3)
                    sortby = "unitPrice";
                  else  sortby = "stockQuantity";
            String sqlst = "SELECT * FROM product ORDER BY "+sortby;

            PreparedStatement ps = con.prepareStatement(sqlst);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                Integer idCategory = rs.getInt("Category_idCategory");
                
                Category category = this.getChildrenCategoryFromId(idCategory);    
                
                Product product = new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl, category);
                
                result.add(product);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    public Category getChildrenCategoryFromId(int id) {
        Category result = new Category();

        try {
            String sqlst = "SELECT * FROM category WHERE idCategory = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                result = new Category(idCategory, name, description); 
            }

        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
}
